package com.kgc.dao;

import com.kgc.entity.Room;
import com.kgc.entity.UserRoom;
import com.kgc.utils.PageUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoomDao extends BaseDao{
    public List<Room> selectPage(PageUtils pageUtils, String status) {
        List<Room> list = new ArrayList<>();
        try {
            String sql = "select r.*,d.name from room r left join danyuan d on r.danyuan_id = d.id ";
            if (StringUtils.isNotEmpty(status)) { // 字符串是否为空
                sql += " where r.status like '%" + status + "%'";
            }
            sql += " limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Room(rs.getInt(1), rs.getString(2), rs.getString(3),rs.getString(4),rs.getInt(5),rs.getString(6)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public int totalCount(String status) {
        try {
            String sql = "select count(*) from room ";
            if (StringUtils.isNotEmpty(status)) { // 字符串是否为空
                sql += " where status like '%" + status + "%'";
            }
            ResultSet rs = super.getExecuteQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public int addRoom(Room room) {
        String sql = "INSERT INTO `room`(`name`,`area`,`status`,`danyuan_id`) VALUES (?, ?,?,?)";
        return super.getExecuteUpdate(sql, room.getName(),room.getArea(),room.getStatus(), room.getDanyuanId());
    }

    public int deleteRoom(String id) {
        String sql = "delete from `room` where id = ?";
        return super.getExecuteUpdate(sql, id);
    }

    public int updateRoom(Room room) {
        String sql = "UPDATE `room` SET `name` = ?, `area`=?,`status`=?,`danyuan_id` = ? WHERE `id` = ?";
        return super.getExecuteUpdate(sql, room.getName(), room.getArea(), room.getStatus(),room.getDanyuanId(),room.getId());
    }

    public List<UserRoom> selectUserPage(PageUtils pageUtils, String roomId) {
        List<UserRoom> list = new ArrayList<>();
        try {
            String sql = "select ur.*,u.username from user_room ur left join user u on ur.user_id = u.id where ur.room_id=? ";
            sql += " limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, roomId,pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new UserRoom(rs.getInt(1), rs.getInt(2), rs.getInt(3),rs.getDate(4),rs.getDate(4),rs.getString(6)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public int userTotalCount(String roomId) {
        try {
            String sql = "select count(*) from user_room where room_id=?";
            ResultSet rs = super.getExecuteQuery(sql,roomId);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }
}

